﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ShengYueWorkShop.BLL.PublicHelper
{
    /// <summary>
    /// 数据库帮助类
    /// </summary>
    public class SqlHelper
    {
        public static string _SqlConn { get; set; }
        /// <summary>
        /// 测试数据库
        /// <para>字符串格式：server=127.0.0.1;database=DATABASE;uid=sa;pwd=</para>
        /// </summary>
        /// <param name="pDbConnStr">数据连接字符串</param>
        /// <returns></returns>
        public List<string> TestDbLink(string pDbConnStr)
        {
            List<string> fListDbName = new List<string>();
            //打开连接参数
            SqlConnection sqlConn = new SqlConnection(pDbConnStr);
            try
            {
                sqlConn.Open();
                string fSqlStr = "Select Name From sysdatabases";
                DataTable fdt = GetDataTable(fSqlStr, pDbConnStr);
                if (fdt.Rows.Count > 0)
                {
                    foreach (DataRow fRow in fdt.Rows)
                    {
                        fListDbName.Add(fRow[0].ToString());
                    }
                }
            }
            catch (SystemException ex)
            {
                throw (ex);
            }
            finally
            {
                //无论是否能连接上主数据库，都关闭连接
                sqlConn.Close();
            }
            return fListDbName;
        }
        /// <summary>
        /// 将连接字符串加密后写到appconfig配置文件中
        /// <para>字符串格式：server=127.0.0.1;database=DATABASE;uid=sa;pwd=</para>
        /// </summary>
        /// <param name="pDbConnStr">数据连接字符串</param>
        /// <returns></returns>
        public bool SaveSqlConnForAppConfig(string pDbConnStr)
        {
            bool flag = false;
            if (pDbConnStr.Length > 0)
            {
                try
                {
                    AppConfig.WriteValue_Encry(pDbConnStr, "SqlConn");
                    flag = true;
                }
                catch (Exception ex)
                {
                    throw (ex);
                }
            }
            return flag;
        }
        /// <summary>
        /// 查询数据 使用默认的连接字符串_SqlConn
        /// </summary>
        /// <param name="pStrSql">SQL语句</param>
        /// <returns></returns>
        public DataTable GetDataTable(string pStrSql)
        {
            return GetDataTable(pStrSql, _SqlConn);
        }
        /// <summary>
        /// 查询数据
        /// </summary>
        /// <param name="pStrSql">SQL语句</param>
        /// <param name="pSqlConn">数据库连接字符串</param>
        /// <returns></returns>
        public DataTable GetDataTable(string pStrSql, string pSqlConn)
        {
            if (pSqlConn.Length == 0 || pStrSql.Length == 0)
            {
                return new DataTable();
            }
            else
            {
                DataSet fDs = GetDataSet(pStrSql, pSqlConn);
                return fDs.Tables[0];
            }
        }
        /// <summary>
        /// 查询数据
        /// </summary>
        /// <param name="pStrSql">SQL语句</param>
        /// <param name="pSqlConn">数据库连接字符串</param>
        /// <returns></returns>
        private DataSet GetDataSet(string pStrSql, string pSqlConn)
        {
            using (SqlConnection connection = new SqlConnection(pSqlConn))
            {
                DataSet ds = new DataSet();
                try
                {
                    connection.Open();
                    SqlDataAdapter command = new SqlDataAdapter(pStrSql, connection);
                    command.Fill(ds, "ds");
                }
                catch (SqlException ex)
                {
                    connection.Close();
                    throw new Exception(ex.Message);
                }
                return ds;
            }
        }
        /// <summary>
        /// 执行简单的SQL语句
        /// </summary>
        /// <param name="_SqlStr"></param>
        /// <returns></returns>
        private int ExecuteSql(string _SqlStr)
        {
            using (SqlConnection connection = new SqlConnection(_SqlConn))
            {
                using (SqlCommand cmd = new SqlCommand(_SqlStr, connection))
                {
                    try
                    {
                        connection.Open();
                        int _rows = cmd.ExecuteNonQuery();
                        return _rows;
                    }
                    catch (SqlException ex)
                    {
                        connection.Close();
                        throw new Exception(ex.Message);
                    }
                }
            }
        }

        public bool UpdateServer(DataModel.WS_FtpServer fModel)
        {
            bool flag = true;
            StringBuilder fSqlStr = new StringBuilder();
            fSqlStr.AppendLine(" Select * From WS_FtpServer Where ");
            fSqlStr.AppendFormat("MachineID='{0}'", fModel.MachineID);
            DataTable fdt = GetDataTable(fSqlStr.ToString());
            if (fdt.Rows.Count == 0)
            {
                fSqlStr.Clear();
                fSqlStr.AppendLine("Insert Into WS_FtpServer(MachineID,ServerIP,ServerPort,UID,Pwd,token)");
                fSqlStr.AppendFormat("Values('{0}','{1}','{2}','{3}','{4}','{4}')", 
                    fModel.MachineID, 
                    fModel.ServerIP, 
                    fModel.ServerPort,
                    fModel.UID,
                    fModel.PWD,
                    fModel.Token);
            }
            else
            {
                if (string.Format("{0}", fdt.Rows[0]["ServerIP"]) == fModel.ServerIP &&
                   string.Format("{0}", fdt.Rows[0]["ServerPort"]) == fModel.ServerPort &&
                   string.Format("{0}", fdt.Rows[0]["UID"]) == fModel.UID &&
                   string.Format("{0}", fdt.Rows[0]["Pwd"]) == fModel.PWD &&
                   string.Format("{0}", fdt.Rows[0]["token"]) == fModel.Token)
                {
                    return flag;
                }
                fSqlStr.Clear();
                fSqlStr.AppendLine("Update WS_FtpServer set");
                fSqlStr.AppendFormat(" ServerIP='{0}',ServerPort='{1}', UID='{2}',Pwd='{3}',token='{4}' ", 
                    fModel.ServerIP, 
                    fModel.ServerPort,
                    fModel.UID,
                    fModel.PWD,
                    fModel.Token);
                fSqlStr.AppendLine("");
                fSqlStr.AppendFormat(" Where FIntID={0}", fdt.Rows[0]["FIntID"]);
            }

            try
            {
                ExecuteSql(fSqlStr.ToString());
            }
            catch (Exception ex)
            {
                flag = false;
                throw (ex);
            }
            return flag;


        }
        /// <summary>
        /// 获取FTP服务器列表
        /// </summary>
        /// <returns></returns>
        public DataTable GetFtpServer()
        {
            string fSqlStr = string.Format("Select *  from  WS_FtpServer");
            return GetDataTable(fSqlStr);
        }
    }
}
